package com.imut.lagain.repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.imut.lagain.entity.AnswerLockReminder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 答案锁提醒数据访问层
 */
@Mapper
public interface AnswerLockReminderRepository extends BaseMapper<AnswerLockReminder> {

    /**
     * 根据锁ID查询提醒
     */
    List<AnswerLockReminder> findByLockIdAndDeletedAtIsNullOrderByReminderTimeAsc(Long lockId);

    /**
     * 根据用户ID查询提醒
     */
    List<AnswerLockReminder> findByUserIdAndDeletedAtIsNullOrderByReminderTimeAsc(Long userId);

    /**
     * 根据锁ID和用户ID查询提醒
     */
    List<AnswerLockReminder> findByLockIdAndUserIdAndDeletedAtIsNullOrderByReminderTimeAsc(Long lockId, Long userId);

    /**
     * 根据提醒类型查询提醒
     */
    List<AnswerLockReminder> findByReminderTypeAndDeletedAtIsNullOrderByReminderTimeAsc(String reminderType);

    /**
     * 根据用户ID和提醒类型查询提醒
     */
    List<AnswerLockReminder> findByUserIdAndReminderTypeAndDeletedAtIsNullOrderByReminderTimeAsc(Long userId, String reminderType);

    /**
     * 根据提醒状态查询提醒
     */
    List<AnswerLockReminder> findByReminderStatusAndDeletedAtIsNullOrderByReminderTimeAsc(String reminderStatus);

    /**
     * 根据用户ID和提醒状态查询提醒
     */
    List<AnswerLockReminder> findByUserIdAndReminderStatusAndDeletedAtIsNullOrderByReminderTimeAsc(Long userId, String reminderStatus);

    /**
     * 查询需要发送的提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE reminder_time <= #{currentTime} AND reminder_status = 'active' AND is_sent = 0 AND deleted_at IS NULL ORDER BY priority DESC, reminder_time ASC")
    List<AnswerLockReminder> findRemindersToSend(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查询即将到期的提醒（指定时间内）
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE reminder_time BETWEEN #{currentTime} AND #{endTime} AND reminder_status = 'active' AND is_sent = 0 AND deleted_at IS NULL ORDER BY reminder_time ASC")
    List<AnswerLockReminder> findUpcomingReminders(@Param("currentTime") LocalDateTime currentTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 查询过期的提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE expires_at <= #{currentTime} AND reminder_status != 'expired' AND deleted_at IS NULL")
    List<AnswerLockReminder> findExpiredReminders(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 查询发送失败的提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE delivery_status = 'failed' AND delivery_attempts < max_delivery_attempts AND deleted_at IS NULL ORDER BY last_delivery_attempt ASC")
    List<AnswerLockReminder> findFailedRemindersForRetry();

    /**
     * 查询需要响应的提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE response_required = 1 AND response_received = 0 AND is_sent = 1 AND deleted_at IS NULL ORDER BY sent_at ASC")
    List<AnswerLockReminder> findRemindersAwaitingResponse();

    /**
     * 根据优先级查询提醒
     */
    List<AnswerLockReminder> findByPriorityAndDeletedAtIsNullOrderByReminderTimeAsc(Integer priority);

    /**
     * 根据紧急程度查询提醒
     */
    List<AnswerLockReminder> findByUrgencyLevelAndDeletedAtIsNullOrderByReminderTimeAsc(String urgencyLevel);

    /**
     * 根据投递方式查询提醒
     */
    List<AnswerLockReminder> findByDeliveryMethodAndDeletedAtIsNullOrderByReminderTimeAsc(String deliveryMethod);

    /**
     * 根据模板ID查询提醒
     */
    List<AnswerLockReminder> findByTemplateIdAndDeletedAtIsNullOrderByReminderTimeAsc(String templateId);

    /**
     * 统计锁的提醒数量
     */
    @Select("SELECT COUNT(*) FROM answer_lock_reminder WHERE lock_id = #{lockId} AND deleted_at IS NULL")
    Long countByLockId(@Param("lockId") Long lockId);

    /**
     * 统计用户的提醒数量
     */
    @Select("SELECT COUNT(*) FROM answer_lock_reminder WHERE user_id = #{userId} AND deleted_at IS NULL")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 统计用户各提醒类型的数量
     */
    @Select("SELECT reminder_type, COUNT(*) FROM answer_lock_reminder WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY reminder_type")
    List<Object[]> countByUserIdGroupByReminderType(@Param("userId") Long userId);

    /**
     * 统计用户各提醒状态的数量
     */
    @Select("SELECT reminder_status, COUNT(*) FROM answer_lock_reminder WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY reminder_status")
    List<Object[]> countByUserIdGroupByReminderStatus(@Param("userId") Long userId);

    /**
     * 统计用户各投递状态的数量
     */
    @Select("SELECT delivery_status, COUNT(*) FROM answer_lock_reminder WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY delivery_status")
    List<Object[]> countByUserIdGroupByDeliveryStatus(@Param("userId") Long userId);

    /**
     * 查询用户最近的提醒
     */
    List<AnswerLockReminder> findTop10ByUserIdAndDeletedAtIsNullOrderByCreatedAtDesc(Long userId);

    /**
     * 查询锁最近的提醒
     */
    List<AnswerLockReminder> findTop5ByLockIdAndDeletedAtIsNullOrderByCreatedAtDesc(Long lockId);

    /**
     * 查询用户提醒发送成功率
     */
    @Select("SELECT (COUNT(CASE WHEN delivery_status = 'delivered' THEN 1 END) * 100.0 / COUNT(*)) FROM answer_lock_reminder WHERE user_id = #{userId} AND is_sent = 1 AND deleted_at IS NULL")
    Double getDeliverySuccessRateByUserId(@Param("userId") Long userId);

    /**
     * 查询用户提醒响应率
     */
    @Select("SELECT (COUNT(CASE WHEN response_received = 1 THEN 1 END) * 100.0 / COUNT(*)) FROM answer_lock_reminder WHERE user_id = #{userId} AND response_required = 1 AND deleted_at IS NULL")
    Double getResponseRateByUserId(@Param("userId") Long userId);

    /**
     * 查询用户平均响应时间
     */
    @Select("SELECT AVG(TIMESTAMPDIFF(MINUTE, sent_at, response_time)) FROM answer_lock_reminder WHERE user_id = #{userId} AND response_received = 1 AND deleted_at IS NULL")
    Double getAverageResponseTimeByUserId(@Param("userId") Long userId);

    /**
     * 查询用户最常用的投递方式
     */
    @Select("SELECT delivery_method FROM answer_lock_reminder WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY delivery_method ORDER BY COUNT(*) DESC")
    List<String> getMostUsedDeliveryMethodsByUserId(@Param("userId") Long userId);

    /**
     * 查询用户偏好的提醒时间
     */
    @Select("SELECT HOUR(reminder_time) as hour, COUNT(*) as count FROM answer_lock_reminder WHERE user_id = #{userId} AND deleted_at IS NULL GROUP BY HOUR(reminder_time) ORDER BY COUNT(*) DESC")
    List<Object[]> getPreferredReminderTimesByUserId(@Param("userId") Long userId);

    /**
     * 查询高优先级提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE priority >= #{minPriority} AND reminder_status = 'active' AND deleted_at IS NULL ORDER BY priority DESC, reminder_time ASC")
    List<AnswerLockReminder> findHighPriorityReminders(@Param("minPriority") Integer minPriority);

    /**
     * 查询紧急提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE urgency_level IN ('high', 'critical') AND reminder_status = 'active' AND deleted_at IS NULL ORDER BY urgency_level DESC, reminder_time ASC")
    List<AnswerLockReminder> findUrgentReminders();

    /**
     * 查询测试提醒
     */
    List<AnswerLockReminder> findByIsTestTrueAndDeletedAtIsNullOrderByCreatedAtDesc();

    /**
     * 查询活动提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE campaign_id = #{campaignId} AND deleted_at IS NULL ORDER BY reminder_time ASC")
    List<AnswerLockReminder> findByCampaignId(@Param("campaignId") String campaignId);

    /**
     * 查询带标签的提醒
     */
    @Select("SELECT * FROM answer_lock_reminder WHERE tags LIKE CONCAT('%', #{tag}, '%') AND deleted_at IS NULL ORDER BY reminder_time ASC")
    List<AnswerLockReminder> findByTag(@Param("tag") String tag);

    /**
     * 查询系统提醒统计
     */
    @Select("SELECT COUNT(*), COUNT(CASE WHEN is_sent = 1 THEN 1 END), COUNT(CASE WHEN delivery_status = 'delivered' THEN 1 END), COUNT(CASE WHEN response_received = 1 THEN 1 END) FROM answer_lock_reminder WHERE deleted_at IS NULL")
    Object[] getSystemReminderStatistics();

    /**
     * 查询最活跃的提醒用户
     */
    @Select("SELECT user_id, COUNT(*) as reminderCount FROM answer_lock_reminder WHERE created_at >= #{threshold} AND deleted_at IS NULL GROUP BY user_id ORDER BY reminderCount DESC")
    List<Object[]> getMostActiveReminderUsers(@Param("threshold") LocalDateTime threshold);

    /**
     * 查询投递方式分布
     */
    @Select("SELECT delivery_method, COUNT(*) FROM answer_lock_reminder WHERE deleted_at IS NULL GROUP BY delivery_method ORDER BY COUNT(*) DESC")
    List<Object[]> getDeliveryMethodDistribution();

    /**
     * 批量更新过期提醒状态
     */
    @Update("UPDATE answer_lock_reminder SET reminder_status = 'expired' WHERE expires_at <= #{currentTime} AND reminder_status != 'expired'")
    int updateExpiredReminders(@Param("currentTime") LocalDateTime currentTime);

    /**
     * 批量更新发送状态
     */
    @Update("UPDATE answer_lock_reminder SET is_sent = 1, sent_at = #{currentTime}, delivery_status = 'sent' WHERE id IN (${reminderIds})")
    int updateSentStatus(@Param("reminderIds") List<Long> reminderIds, @Param("currentTime") LocalDateTime currentTime);

    /**
     * 批量增加投递尝试次数
     */
    @Update("UPDATE answer_lock_reminder SET delivery_attempts = delivery_attempts + 1, last_delivery_attempt = #{currentTime} WHERE id IN (${reminderIds})")
    int incrementDeliveryAttempts(@Param("reminderIds") List<Long> reminderIds, @Param("currentTime") LocalDateTime currentTime);
}